import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DataManager {
    private static final Logger LOGGER = Logger.getLogger(DataManager.class.getName());
    private static List<String> incomeList = new ArrayList<>();
    private static List<String> expenseList = new ArrayList<>();
    private static List<String> budgetList = new ArrayList<>();

    static {
        // 从数据库加载初始数据
        loadIncomeData();
        loadExpenseData();
        loadBudgetData();
    }

    public static void addIncome(String source, String time, String amount) {
        if (isValidDate(time) && isValidAmount(amount)) {
            incomeList.add("来源: " + source + ", 时间: " + time + ", 金额: " + amount);
            saveIncomeToDatabase(source, time, amount);
        } else {
            if (!isValidDate(time)) {
                LOGGER.warning("无效的日期格式: " + time);
            }
            if (!isValidAmount(amount)) {
                LOGGER.warning("无效的金额格式: " + amount);
            }
        }
    }

    public static void addExpense(String source, String time, String amount) {
        if (isValidDate(time) && isValidAmount(amount)) {
            expenseList.add("来源: " + source + ", 时间: " + time + ", 金额: " + amount);
            saveExpenseToDatabase(source, time, amount);
        } else {
            if (!isValidDate(time)) {
                LOGGER.warning("无效的日期格式: " + time);
            }
            if (!isValidAmount(amount)) {
                LOGGER.warning("无效的金额格式: " + amount);
            }
        }
    }

    public static void addBudget(String daily, String monthly, String yearly) {
        if (isValidAmount(daily) && isValidAmount(monthly) && isValidAmount(yearly)) {
            budgetList.add("日预算: " + daily + ", 月预算: " + monthly + ", 年预算: " + yearly);
            saveBudgetToDatabase(daily, monthly, yearly);
        } else {
            if (!isValidAmount(daily)) {
                LOGGER.warning("无效的日预算金额格式: " + daily);
            }
            if (!isValidAmount(monthly)) {
                LOGGER.warning("无效的月预算金额格式: " + monthly);
            }
            if (!isValidAmount(yearly)) {
                LOGGER.warning("无效的年预算金额格式: " + yearly);
            }
        }
    }

    // 获取收入、支出和预算的方法
    public static List<String> getIncomes() {
        return incomeList;
    }

    public static List<String> getExpenses() {
        return expenseList;
    }

    public static List<String> getBudgets() {
        return budgetList;
    }

    public static double getTotalIncome() {
        return incomeList.stream()
                .mapToDouble(s -> Double.parseDouble(s.split(", 金额: ")[1]))
                .sum();
    }

    public static double getTotalExpense() {
        return expenseList.stream()
                .mapToDouble(s -> Double.parseDouble(s.split(", 金额: ")[1]))
                .sum();
    }

    public static double getBalance() {
        return getTotalIncome() - getTotalExpense();
    }

    // 数据库操作方法
    private static void saveIncomeToDatabase(String source, String time, String amount) {
        String sql = "INSERT INTO income(source, time, amount) VALUES(?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, source);
            pstmt.setDate(2, Date.valueOf(time));
            pstmt.setBigDecimal(3, new BigDecimal(amount));
            pstmt.executeUpdate();
        } catch (SQLException | IllegalArgumentException e) {
            LOGGER.log(Level.SEVERE, "保存收入数据到数据库失败", e);
        }
    }

    private static void saveExpenseToDatabase(String source, String time, String amount) {
        String sql = "INSERT INTO expense(source, time, amount) VALUES(?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, source);
            pstmt.setDate(2, Date.valueOf(time));
            pstmt.setBigDecimal(3, new BigDecimal(amount));
            pstmt.executeUpdate();
        } catch (SQLException | IllegalArgumentException e) {
            LOGGER.log(Level.SEVERE, "保存支出数据到数据库失败", e);
        }
    }

    private static void saveBudgetToDatabase(String daily, String monthly, String yearly) {
        String sql = "INSERT INTO budget(daily_budget, monthly_budget, yearly_budget) VALUES(?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setBigDecimal(1, new BigDecimal(daily));
            pstmt.setBigDecimal(2, new BigDecimal(monthly));
            pstmt.setBigDecimal(3, new BigDecimal(yearly));
            pstmt.executeUpdate();
        } catch (SQLException | IllegalArgumentException e) {
            LOGGER.log(Level.SEVERE, "保存预算数据到数据库失败", e);
        }
    }

    // 加载数据库中的现有数据
    private static void loadIncomeData() {
        String sql = "SELECT * FROM income";
        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                String source = rs.getString("source");
                String time = rs.getDate("time").toString();
                String amount = rs.getBigDecimal("amount").toString();
                incomeList.add("来源: " + source + ", 时间: " + time + ", 金额: " + amount);
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "加载收入数据从数据库失败", e);
        }
    }

    private static void loadExpenseData() {
        String sql = "SELECT * FROM expense";
        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                String source = rs.getString("source");
                String time = rs.getDate("time").toString();
                String amount = rs.getBigDecimal("amount").toString();
                expenseList.add("来源: " + source + ", 时间: " + time + ", 金额: " + amount);
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "加载支出数据从数据库失败", e);
        }
    }

    private static void loadBudgetData() {
        String sql = "SELECT * FROM budget";
        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                String daily = rs.getBigDecimal("daily_budget").toString();
                String monthly = rs.getBigDecimal("monthly_budget").toString();
                String yearly = rs.getBigDecimal("yearly_budget").toString();
                budgetList.add("日预算: " + daily + ", 月预算: " + monthly + ", 年预算: " + yearly);
            }
        } catch (SQLException e) {
            LOGGER.log(Level.SEVERE, "加载预算数据从数据库失败", e);
        }
    }

    // 根据时间范围获取支出数据
    public static List<String> getExpensesByTimeRange(String startDate, String endDate) {
        List<String> expensesInRange = new ArrayList<>();
        String sql = "SELECT * FROM expense WHERE time BETWEEN ? AND ?";
        if (isValidDate(startDate) && isValidDate(endDate)) {
            try (Connection conn = DBUtil.getConnection();
                 PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setDate(1, Date.valueOf(startDate));
                pstmt.setDate(2, Date.valueOf(endDate));
                ResultSet rs = pstmt.executeQuery();
                while (rs.next()) {
                    String source = rs.getString("source");
                    String time = rs.getDate("time").toString();
                    String amount = rs.getBigDecimal("amount").toString();
                    expensesInRange.add("来源: " + source + ", 时间: " + time + ", 金额: " + amount);
                }
            } catch (SQLException | IllegalArgumentException e) {
                LOGGER.log(Level.SEVERE, "根据时间范围获取支出数据失败", e);
            }
        } else {
            LOGGER.warning("无效的日期范围: " + startDate + " - " + endDate);
        }
        return expensesInRange;
    }

    // 验证日期格式
    private static boolean isValidDate(String date) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            sdf.parse(date);
            return true;
        } catch (ParseException e) {
            return false;
        }
    }

    // 验证金额格式
    private static boolean isValidAmount(String amount) {
        try {
            Double.parseDouble(amount);
            return true;
        } catch (NumberFormatException e) {
            return false;
        }
    }
}